import openpyxl
from Basic.base_log import log
import shutil
from Basic.PATH import *
# 列表list[1,'a'],字典dict{'name':3},元组tup()
class OpenpyxlExcel():

    def __init__(self,filename=None,sheetname=None):
        '''实例化excel'''
        if filename:
            self.filename = filename
            self.excel = openpyxl.load_workbook(filename)
        if sheetname:
            self.sheetname = sheetname
            self.sheet = self.excel[sheetname]
        shutil.copyfile(filename, os.path.join(Conf_path, "selenium_case_back.xlsx"))
        # self.sheets = excel[self.sheetname]
        # 获取sheet（工作表）行（row）、列（col）数
        # self.nrows = self.sheets.max_row  # 行
        # self.ncols = self.sheets.max_column  # 列
        # print('总列数ncols ： %s' % self.ncols)
        # print('总行数nrows ： %s' % self.nrows)



    # 列表list[1,'a'],字典dict{'name':3},元组tup()
    #实例化excel
    def get_excel(self,filename):
        return openpyxl.load_workbook(filename)
    #实例化工作表
    def get_sheet(self,filename,sheetname):
        excel = openpyxl.load_workbook(filename)
        # log.info(sheetname)
        sheet = excel[sheetname]
        return sheet

    def get_cell(self, sheet, row, col):
        ''' 获取单元格的值
                           row   ：行
                           cols  ：列
                           value ：要写入的值
        '''
        return sheet.cell(row, col).value

    #获取所有工作表名称
    def get_sheetnames(self,filename):
        excel = openpyxl.load_workbook(filename)
        sheetnames = excel.get_sheet_names()
        return sheetnames


    def get_caseMoud_rows(self,filename,sheetname,case='LOGIN'):
        '''获取相关用例模块的所在行数'''
        self.lrows = []
        self.sheet = self.get_sheet(filename,sheetname)
        self.nrows = self.sheet.max_row  # 行
        for i in range(1,self.nrows):
            ele = self.sheet.cell(i, 1).value  # 获取第i行0列的单元格值
            # print('i = %s  ele = %s' % (str(i),ele))
            if ele == case:
                self.lrows.append(i)
        # print('用例 %s 所在行数区间 lrows ： %s' % (case, self.lrows))
        return self.lrows


    def get_caseMoud_AllLine(self,filename,sheetname):
        ''' 获取所有用例模块及其所在行数范围 '''
        list = []
        dict_case = {}
        flag = None
        self.sheet = self.get_sheet(filename,sheetname)
        self.nrows = self.sheet.max_row + 1  # 最大行数
        for i in range(2, self.nrows):
            ele = self.sheet.cell(i, 1).value  # 获取第i行0列的单元格值
            if ele == None:
                continue
            # print('i = %s  ele = %s' % (str(i),ele))
            if ele != flag:
                # print('---------%s->  flag:%s != ele:%s' % (i, flag, ele))
                flag = ele
                list = []
                list.append(i)
                dict_case[ele] = list
            else:
                # print('--%s->  flag:%s == ele:%s'%(i,flag,ele))
                list.append(i)
            # print('dict_case:%s' % dict_case)
        return dict_case


    def get_caseMoudleAll_Name(self,filename,sheetname):
        ''' 获取用例模块名称 '''
        caseList  = []
        dict_all = self.get_caseMoud_AllLine(filename,sheetname)  # 获取所有用例模块
        for key in dict_all:  # 遍历所有用例模块
            caseList .append(key)  # 将模块添加到要执行的用例模块集合
        return caseList


    def clear_cols(self,filename,sheetname,startrow=2,endrow=None,col=16):
        ''' 清空整列内容
           startrow：开始行数
           endrow  ：结束行数，默认所有
           col     ：要清空的列
        '''
        self.excel = openpyxl.load_workbook(filename)  # 打开excel文件
        self.sheet = self.excel[sheetname]  # 获取excel工作表
        self.nrows = self.sheet.max_row  # 行
        if endrow == None:
            endrow =self.nrows #所有行
        for i in range(startrow,endrow):
            # print('清空第 %s 行，第 %s 列'% (i-1,col))
            self.sheet.cell(i, col).value = ''  # 在对应行数写入值，从1开始计算
        self.excel.save(filename)  # 保存


    def write_excel(self,filename,sheetname,row, cols, value):
        ''' 写入数据
           row   ：行
           cols  ：列
           value ：要写入的值
        '''
        self.excel = openpyxl.load_workbook(filename) # 打开excel文件
        self.sheet = self.excel[sheetname]   # 获取excel工作表
        self.sheet.cell(row,cols).value = value # 在对应行数写入值，从1开始计算
        self.excel.save(filename) # 保存

    def get_precondition_assertresult(self, dict, sheet):
        ''' 获取前提条件中填写的编号的对应行数的结果，如果为否则跳过测试
                   row   ：行
                   cols  ：列
                   value ：要写入的值
        '''
        # 遍历前提条件包含的行数，判断此行的断言结果是否存在False
        stra = str(dict['前提条件'])
        # log.info('--    -->前提条件：%s'%stra)
        if '.' in stra:
            # log.info('前提条件：%s' % (stra.split('.')))
            for i in stra.split('.'):
                result = sheet.cell(int(i)+1, 15).value
                if result != 'PASS':
                    return result
        elif ',' in stra:
            # log.info('前提条件：%s' % (stra.split(',')))
            for i in stra.split(','):
                result = sheet.cell(int(i)+1, 15).value
                if result == 'PASS':
                    return result
        elif stra and stra != 'None':
                result = sheet.cell(int(stra)+1, 15).value
                # log.info('--    -->result：%s' % result)
                if result != 'PASS':
                    return result
        return 'PASS'

    def get_row_all(self, filename, sheetname):
        """获取所有行的数据，返回一个字典"""
        dict_all = {}
        self.sheet = self.get_sheet(filename, sheetname)
        rows = self.get_caseMoud_rows(filename, sheetname)
        for i in rows:
            if i == 1:
                continue
            dict = self.get_row_cells(filename, sheetname, i)
            dict_all[i - 1] = dict
        return dict_all

    def get_row_cells(self,filename,sheetname, rows=1):
        ''' 获取excel第 rows行的所有值，返回一个字典
           rows   ：行
        '''
        self.dict = {}
        self.sheet = self.get_sheet(filename,sheetname)
        # 获取相关用例的所在行数的所有单元格值
        self.dict['用例模块'] = self.sheet.cell(rows, 1).value
        self.dict['编号'] =     self.sheet.cell(rows, 2).value
        self.dict['前提条件'] = self.sheet.cell(rows, 3).value
        self.dict['操作步骤'] = self.sheet.cell(rows, 4).value
        self.dict['操作元素'] = self.sheet.cell(rows, 5).value
        self.dict['元素定位方式'] = self.sheet.cell(rows, 6).value
        self.dict['元素动作'] = self.sheet.cell(rows, 7).value
        self.dict['输入数据'] = self.sheet.cell(rows, 8).value
        # self.dict['延时'] =     self.sheet.cell(rows, 9).value
        self.dict['是否执行'] = self.sheet.cell(rows, 9).value

        self.dict['断言元素'] = self.sheet.cell(rows, 10).value
        self.dict['断言元素方法'] = self.sheet.cell(rows, 11).value
        self.dict['断言元素动作'] = self.sheet.cell(rows, 12).value
        self.dict['断言方法'] = self.sheet.cell(rows, 13).value
        self.dict['预期结果'] = self.sheet.cell(rows, 14).value
        self.dict['断言结果'] = self.sheet.cell(rows, 15).value
        self.dict['测试时间'] = self.sheet.cell(rows, 16).value
        return self.dict


if __name__ == "__main__":
    # driver = webdriver.Chrome()
    # print(os.path)
    # SL = OpenpyxlExcel()
    # filename= 'E:\GONGJU\Python\PycharmProjects\SeleniumUiniTest UiFramework 20200814\Conf\SL_Test_Element.xlsx'
    # a =SL.get_sheetnames(filename=filename)
    # b=SL.get_row_cells(filename=filename,sheetname='100',rows=3)
    # print(a)
    # print(b)
    pass

